﻿using System;
using System.Linq;
using MongoDB.Bson;
using SupermarketModel;
using Supermarket.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.IO;

namespace Supermarket.Client
{
    public class ProductReports
    {
        public class ExcelField
        {
            public string Vendor { get; set; }
            public decimal? Incomes { get; set; }
            public decimal? Expenses { get; set; }
            public decimal? Taxes { get; set; }
            public decimal? Result { get; set; }

            public bool CheckVendor(string Vendor)
            {
                if (this.Vendor == Vendor)
                {
                    return true;
                }
                return false;
            }
        }

        public static void FillExcelReport()
        {
            List<ExcelField> fields = new List<ExcelField>();
            var sqlDb = new SupermarketContext();
            var sqLiteDb = new SqLiteReportSalesEntities();

            using (sqLiteDb)
            {
                using (sqlDb)
                {
                    var reports =
                        from productSale in sqLiteDb.ProductSales
                        join tax in sqLiteDb.Taxes
                        on productSale.ProductName equals tax.ProductName
                        select new
                        {
                            Vendor = productSale.VendorName,
                            Income = productSale.TotalIncomes,
                            Tax = tax.Tax1
                        };
                    foreach (var report in reports)
                    {
                        int index = fields.FindIndex(x => x.Vendor == report.Vendor);

                        var field = new ExcelField();
                        field.Vendor = report.Vendor;
                        field.Incomes = report.Income;
                        field.Taxes = field.Incomes * (decimal.Parse(report.Tax.Replace('%', ' ')) / 100);

                        var result = sqlDb.VendorExpenses.Where(x => x.Vendor.Name == report.Vendor && x.Month.Month == DateTime.Now.Month).FirstOrDefault();
                        field.Expenses = result.Expenses;

                        field.Result = field.Incomes - field.Expenses - field.Taxes;
                        if (index < 0)
                        {
                            fields.Add(field);
                        }
                        else
                        {
                            fields[index].Incomes += field.Incomes;
                            fields[index].Expenses += field.Expenses;
                            fields[index].Taxes += field.Taxes;
                            fields[index].Result += field.Result;
                        }
                    }
                }

                if (File.Exists("..\\..\\..\\Products-Total-Report.xlsx"))
                {
                    File.Delete("..\\..\\..\\Products-Total-Report.xlsx");
                }

                OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\..\\..\\Products-Total-Report.xlsx;Extended Properties='Excel 12.0 XML;HDR=Yes'");
                conn.Open();
                using (conn)
                {
                    OleDbCommand cmd = new OleDbCommand("CREATE TABLE [NewSheet] ([Vendor] string, [Incomes] string, [Expenses] string, [Taxes] string, [Financial Result] string)", conn);
                    cmd.ExecuteNonQuery();

                    foreach (var field in fields)
                    {
                        OleDbCommand insertCmd = new OleDbCommand("INSERT INTO [NewSheet$] (Vendor, Incomes, Expenses, Taxes, [Financial Result]) VALUES (@vendor, @income, @expense, @tax, @result)", conn);
                        insertCmd.Parameters.AddWithValue("@vendor", field.Vendor);
                        insertCmd.Parameters.AddWithValue("@income", field.Incomes);
                        insertCmd.Parameters.AddWithValue("@expense", field.Expenses);
                        insertCmd.Parameters.AddWithValue("@tax", field.Taxes);
                        insertCmd.Parameters.AddWithValue("@result", field.Result);
                        insertCmd.ExecuteNonQuery();
                    }
                }
            }
        }

        public static void FillSqLite()
        {
            var db = new SqLiteReportSalesEntities();

            var items = MongoDbProvider.db.LoadData<BsonDocument>();
            foreach (var item in items)
            {
                var newItem = new ProductSale();
                newItem.ProductId = item[1].ToInt32();
                newItem.ProductName = item[2].ToString();
                newItem.VendorName = item[3].ToString();
                newItem.TotalQuantitySold = item[4].ToInt32();
                newItem.TotalIncomes = (decimal)item[5].ToDouble();
                db.ProductSales.Add(newItem);
            }
            db.SaveChanges();
        }
    }
}
